﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Unis.Framework.Core.Exceptions;
using Unis.Framework.Core.Log;
using Unis.Framework.ORM.BaseDefiniens.ConstDefine;
using Unis.Framework.ORM.BaseDefiniens.Enums;
using Unis.Framework.ORM.Mapping;
using Unis.Framework.ORM.Model;
using Unis.Framework.ORM.Persistence;
using Unis.ORM.Mapping;
using Unis.ORM.Persistence;

namespace Unis.Framework.ORM.DbSql
{
    public sealed class SqlShareHelper
    {
        // Fields
        private static Dictionary<string, string> _HS_SQL_SPEC_STRING = new Dictionary<string, string>();
        private static SqlShareHelper _Instance;
        private static object _Obj = new object();
        public static readonly string ORACLE_PARAM_PREFIX = ":";
        private static readonly string SQL_AND = " AND ";
        private static readonly string SQL_AND_NOT = " AND NOT ";
        private static readonly string SQL_LEFT_BRACKET = "(";
        private const int SQL_MAX_LENGTH = 0x1db0;
        private static readonly string SQL_OR = " OR ";
        private static readonly string SQL_OR_NOT = " OR NOT ";
        private static readonly string SQL_RIGHT_BRACKET = ")";
        public static readonly string SQL_SERVER_PARAM_PREFIX = "@";
        public static string[] SQL_SPEC_STRING = new string[] { (SQL_XML_CFG_PARAM_PREFIX + "WHERE"), (SQL_XML_CFG_PARAM_PREFIX + "ORDERBY"), (SQL_XML_CFG_PARAM_PREFIX + "GROUPBY") };
        public static string SQL_XML_CFG_PARAM_PREFIX = SQL_SERVER_PARAM_PREFIX;

        // Methods
        static SqlShareHelper()
        {
            foreach (string str in SQL_SPEC_STRING)
            {
                _HS_SQL_SPEC_STRING.Add(str, str);
            }
        }

        protected SqlShareHelper()
        {
        }

        private void builderSqlStringByParameters(DatabaseType dbaseType, StringBuilder sqlFilter, QueryGroupLinkType linkType, QueryParameterMappings parsMapping, QueryParameterInfo[] queryParams)
        {
            foreach (QueryParameterInfo info in queryParams)
            {
                List<QueryParameterInfo> list = null;
                if (info.IsGroupNode)
                {
                    if ((info.Childs == null) || (info.Childs.Count == 0))
                    {
                        continue;
                    }
                    list = info.Childs.FindAll(o => !o.Limited);
                    if (list.Count == 0)
                    {
                        continue;
                    }
                }
                string str = sqlFilter.ToString().Trim();
                if ((str.Length > 0) && (str.LastIndexOf(SQL_LEFT_BRACKET) != (str.Length - 1)))
                {
                    if (linkType == QueryGroupLinkType.AND)
                    {
                        sqlFilter.Append(SQL_AND);
                    }
                    else if (linkType == QueryGroupLinkType.AndNot)
                    {
                        sqlFilter.Append(SQL_AND_NOT);
                    }
                    else if (linkType == QueryGroupLinkType.OrNot)
                    {
                        sqlFilter.Append(SQL_OR_NOT);
                    }
                    else
                    {
                        sqlFilter.Append(SQL_OR);
                    }
                }
                sqlFilter.Append(SQL_LEFT_BRACKET);
                if (info.IsGroupNode)
                {
                    this.builderSqlStringByParameters(dbaseType, sqlFilter, info.GroupNodeLinkType, parsMapping, list.ToArray());
                }
                else
                {
                    string str2 = string.Empty;
                    if (info.Condition == DataFilterConditions.SqlAppend)
                    {
                        if (info.Value == null)
                        {
                            throw new APPException(string.Format("参数{0} 的DataFilterConditions 设置为 sqlAppend,value 不能为空 ", info.PropertyName), APPMessageType.SysErrInfo);
                        }
                        str2 = info.Value.ToString();
                    }
                    else
                    {
                        str2 = this.singleParameterToSql(dbaseType, parsMapping, info);
                    }
                    sqlFilter.Append(str2);
                }
                sqlFilter.Append(SQL_RIGHT_BRACKET);
                //Label_015E: ;
            }
        }

        public string BuildQueryInSql<T>(IList<T> ids)
        {
            List<string> list = new List<string>();
            foreach (T local in ids)
            {
                string str = local.ToString();
                if (!string.IsNullOrEmpty(str))
                {
                    if ((str.IndexOf("'") != 0) || (str.LastIndexOf("'") != (str.Length - 1)))
                    {
                        str = "'" + str.Replace("'", "''") + "'";
                    }
                    if (!list.Contains(str))
                    {
                        list.Add(str);
                    }
                }
            }
            if (list.Count == 0)
            {
                list.Add("'-1'");
            }
            return string.Join(",", list.ToArray());
        }

        public string BuildQueryInSql<T>(IList<T> ids, string fieldName)
        {
            string str = fieldName + " IN(";
            List<string> list = new List<string>();
            foreach (T local in ids)
            {
                string item = "'" + local.ToString().Replace("'", "''") + "'";
                if (!list.Contains(item))
                {
                    list.Add(item);
                }
            }
            if (list.Count == 0)
            {
                list.Add("'-1'");
            }
            return (str + string.Join(",", list.ToArray()) + ")");
        }

        public string BuildQueryInSql(DataRow[] drsData, string fieldName)
        {
            if (drsData.Length == 0)
            {
                return string.Format("{0} IN ('1')", fieldName);
            }
            string str = fieldName + " IN(";
            List<string> list = new List<string>();
            foreach (DataRow row in drsData)
            {
                string item = row[fieldName].ToString();
                if (!list.Contains(item))
                {
                    list.Add(item);
                    str = str + item + ",";
                }
            }
            return (str.Remove(str.Length - 1, 1) + ")");
        }

        public string BuildQueryInSql(DataRow[] drsData, string fieldName, bool isString)
        {
            if (drsData.Length == 0)
            {
                return string.Format("{0} IN ('1')", fieldName);
            }
            StringBuilder builder = new StringBuilder();
            builder.Append(fieldName);
            builder.Append(" IN(");
            List<string> list = new List<string>();
            foreach (DataRow row in drsData)
            {
                string item = row[fieldName].ToString();
                if (!list.Contains(item))
                {
                    list.Add(item);
                    if (isString)
                    {
                        builder.Append("'").Append(item).Append("',");
                    }
                    else
                    {
                        builder.Append(item).Append(",");
                    }
                }
            }
            builder.Remove(builder.Length - 1, 1);
            builder.Append(")");
            return builder.ToString();
        }

        public string ConvertConditionToSqlStr(DataFilterConditions queryCondition)
        {
            switch (queryCondition)
            {
                case DataFilterConditions.Less:
                    return " < ";

                case DataFilterConditions.Greater:
                    return " > ";

                case DataFilterConditions.Equal:
                case DataFilterConditions.Special:
                    return " = ";

                case DataFilterConditions.NotEqual:
                    return " <> ";

                case DataFilterConditions.Include:
                case DataFilterConditions.BenginsWith:
                case DataFilterConditions.EndsWith:
                case DataFilterConditions.Like:
                    return " LIKE ";

                case DataFilterConditions.NotInclude:
                case DataFilterConditions.NotLike:
                    return " NOT LIKE ";

                case DataFilterConditions.Between:
                    return " BETWEEN ";

                case DataFilterConditions.NotBetween:
                    return " NOT BETWEEN ";

                case DataFilterConditions.GreaterOrEqual:
                    return " >= ";

                case DataFilterConditions.LessOrEqual:
                    return " <= ";

                case DataFilterConditions.IsNull:
                    return " IS NULL ";

                case DataFilterConditions.IsNotNull:
                    return " IS NOT NULL ";

                case DataFilterConditions.In:
                    return " IN ";

                case DataFilterConditions.NotIn:
                    return " NOT IN ";
            }
            return string.Empty;
        }

        public string GetKeyValuesToString(DataRow[] drsData, string keyName)
        {
            StringBuilder builder = new StringBuilder();
            foreach (DataRow row in drsData)
            {
                object orgData = row[keyName];
                if ((orgData != null) && (Convert.ToInt32(orgData) > 0))
                {
                    builder.Append("'" + orgData.ToString() + "',");
                }
            }
            if (builder.Length > 0)
            {
                builder = builder.Remove(builder.Length - 1, 1);
            }
            else
            {
                builder.Append(((short)(-32768)).ToString());
            }
            return builder.ToString();
        }

        public IList<string> GetSqlStringParamsName(string sqlStr)
        {
            string str = ",;) ";
            IList<string> list = new List<string>();
            int num = 0;
            for (int i = 0; i < sqlStr.Length; i++)
            {
                string str2 = sqlStr.Substring(i, 1);
                if (str2.Equals(SQL_XML_CFG_PARAM_PREFIX))
                {
                    num = i;
                }
                else if ((num > 0) && (str.IndexOf(str2) >= 0))
                {
                    string item = sqlStr.Substring(num + 1, (i - num) - 1);
                    if (!list.Contains(item))
                    {
                        list.Add(item);
                    }
                    num = -1;
                }
            }
            return list;
        }

        public string QueryParametersToSqlString(QueryParameterMappings parsMapping, QueryParameterInfo[] queryParams)
        {
            DatabaseType dbaseType = DatabaseConfigurationScope.CreateDatabaseType();
            StringBuilder sqlFilter = new StringBuilder();
            string targetRowCountSqlFilter = DbQueryTargetRowCountScope.GetTargetRowCountSqlFilter(dbaseType);
            if ((queryParams == null) || (queryParams.Length == 0))
            {
                if (string.IsNullOrEmpty(targetRowCountSqlFilter))
                {
                    return "0=0";
                }
                return targetRowCountSqlFilter;
            }
            QueryParameterInfo[] infoArray = Array.FindAll<QueryParameterInfo>(queryParams, o => !o.Limited);
            if (infoArray.Length == 0)
            {
                if (string.IsNullOrEmpty(targetRowCountSqlFilter))
                {
                    return "0=0";
                }
                return targetRowCountSqlFilter;
            }
            this.builderSqlStringByParameters(dbaseType, sqlFilter, QueryGroupLinkType.AND, parsMapping, infoArray);
            string str2 = "(" + sqlFilter.ToString() + ")";
            if (!string.IsNullOrEmpty(targetRowCountSqlFilter))
            {
                str2 = str2 + " AND " + targetRowCountSqlFilter;
            }
            return str2;
        }

        public string ReplaceCanExecSqlString(string sqlStr, IList<string> paramsName, DataRow drData)
        {
            DatabaseType dbaseType = DatabaseConfigurationScope.CreateDatabaseType();
            string str = sqlStr;
            DataColumnCollection columns = drData.Table.Columns;
            foreach (string str2 in paramsName)
            {
                TraceEx.Assert(columns.Contains(str2), "SQL语句中的参数" + str2 + "在数据集中不存在.");
                string str3 = this.valueToSQL(dbaseType, columns[str2].DataType.Name, drData[str2], DataFilterConditions.Equal);
                str = str.Replace(SQL_XML_CFG_PARAM_PREFIX + str2 + ",", str3 + ",");
                str = str.Replace(SQL_XML_CFG_PARAM_PREFIX + str2 + " ", str3 + " ");
                str = str.Replace(SQL_XML_CFG_PARAM_PREFIX + str2 + ";", str3 + "; ");
                str = str.Replace(SQL_XML_CFG_PARAM_PREFIX + str2 + ")", str3 + ")");
            }
            return str;
        }

        public string ReplaceCanExecSqlStringEx(string sqlStr, params object[] parsValue)
        {
            DatabaseType dbaseType = DatabaseConfigurationScope.CreateDatabaseType();
            IList<string> sqlStringParamsName = this.GetSqlStringParamsName(sqlStr);
            string str = sqlStr;
            if (sqlStringParamsName.Count != parsValue.Length)
            {
                throw new APPException("在执行SQL语句" + sqlStr + "时获取的参数和传入的参数不一致，请检查。", APPMessageType.SysErrInfo);
            }
            for (int i = 0; i < sqlStringParamsName.Count; i++)
            {
                object dataValue = parsValue[i];
                if ((dataValue != null) && (string.Compare(sqlStringParamsName[i].ToString(), "WHERE", true) != 0))
                {
                    dataValue = this.valueToSQL(dbaseType, dataValue.GetType().Name, dataValue, DataFilterConditions.Equal);
                }
                else if (dataValue == null)
                {
                    dataValue = "NULL";
                }
                str = str.Replace(SQL_XML_CFG_PARAM_PREFIX + sqlStringParamsName[i].ToString() + " ", dataValue.ToString() + " ").Replace(SQL_XML_CFG_PARAM_PREFIX + sqlStringParamsName[i].ToString() + ",", dataValue.ToString() + ",").Replace(SQL_XML_CFG_PARAM_PREFIX + sqlStringParamsName[i].ToString() + ";", dataValue.ToString() + ";").Replace(SQL_XML_CFG_PARAM_PREFIX + sqlStringParamsName[i].ToString() + ")", dataValue.ToString() + ")");
            }
            return str;
        }

        private string singleParameterToSql(DatabaseType dbaseType, QueryParameterMappings parsMapping, QueryParameterInfo parInfo)
        {
            StringBuilder builder = new StringBuilder();
            string str = ((parsMapping == null) || string.IsNullOrEmpty(parsMapping.DefaultTableAlias)) ? "" : (parsMapping.DefaultTableAlias + ".");
            QueryParameterMappingInfo info = ((parsMapping != null) && parsMapping.ContainsKey(parInfo.PropertyName)) ? parsMapping[parInfo.PropertyName] : null;
            string propertyName = (info == null) ? (str + parInfo.PropertyName) : info.FieldName;
            if (parInfo.Condition == DataFilterConditions.Special)
            {
                propertyName = parInfo.PropertyName;
            }
            if (parInfo.MultiValue)
            {
                string str3 = this.BuildQueryInSql<string>(parInfo.Value.ToString().Split(new char[] { ',' }));
                if (str3.Length > 0x1db0)
                {
                    throw new APPException(string.Format("构造字段{0} 的IN 查询语句时 超长", propertyName));
                }
                return string.Format("{0} IN ({1})", propertyName, str3);
            }
            if (string.Compare(parInfo.DataType, "DateTime", true) == 0)
            {
                builder.Append(string.Format("{0}", propertyName));
            }
            else
            {
                builder.Append(propertyName);
            }
            builder.Append(this.ConvertConditionToSqlStr(parInfo.Condition));
            if ((parInfo.Condition != DataFilterConditions.IsNotNull) && (parInfo.Condition != DataFilterConditions.IsNull))
            {
                if (parInfo.Condition == DataFilterConditions.Between)
                {
                    builder.Append(this.valueToSQL(dbaseType, parInfo.DataType, parInfo.Value, parInfo.Condition));
                    builder.Append(SQL_AND);
                    builder.Append(this.valueToSQL(dbaseType, parInfo.DataType, parInfo.Value2, parInfo.Condition, true));
                }
                else
                {
                    builder.Append(this.valueToSQL(dbaseType, parInfo.DataType, parInfo.Value, parInfo.Condition));
                }
            }
            return builder.ToString();
        }

        public string[] SplitInSqlStringBySqlMaxLength<T>(IList<T> vals)
        {
            List<string> list = new List<string>();
            List<string> list2 = new List<string>();
            int length = 0;
            if (vals.Count == 0)
            {
                return new string[] { "'-1'" };
            }
            foreach (T local in vals)
            {
                length += local.ToString().Length;
                if (length < 0x1db0)
                {
                    list2.Add("'" + local.ToString().Replace("'", "''") + "'");
                }
                else
                {
                    list.Add(string.Join(",", list2.ToArray()));
                    list2.Clear();
                    length = local.ToString().Length;
                    list2.Add("'" + local.ToString().Replace("'", "''") + "'");
                }
            }
            if (list2.Count > 0)
            {
                list.Add(string.Join(",", list2.ToArray()));
            }
            return list.ToArray();
        }

        public string[] SplitInSqlStringByValueCount<T>(IList<T> vals, int splitValueCcount)
        {
            List<string> list = new List<string>();
            List<string> list2 = new List<string>();
            if (vals.Count == 0)
            {
                return new string[] { "'-1'" };
            }
            foreach (T local in vals)
            {
                if (list2.Count < splitValueCcount)
                {
                    list2.Add("'" + local.ToString().Replace("'", "''") + "'");
                }
                else
                {
                    list.Add(string.Join(",", list2.ToArray()));
                    list2.Clear();
                    list2.Add("'" + local.ToString().Replace("'", "''") + "'");
                }
            }
            if (list2.Count > 0)
            {
                list.Add(string.Join(",", list2.ToArray()));
            }
            return list.ToArray();
        }

        private string stringValueToSqlString(object dataValue, DataFilterConditions queryCondition)
        {
            if (queryCondition == DataFilterConditions.Special)
            {
                return dataValue.ToString();
            }
            if ((queryCondition == DataFilterConditions.Include) || (queryCondition == DataFilterConditions.NotInclude))
            {
                return ("'%" + dataValue.ToString().Trim().Replace("'", "''") + "%'");
            }
            if ((queryCondition != DataFilterConditions.In) && (queryCondition != DataFilterConditions.NotIn))
            {
                return ("'" + dataValue.ToString().Trim().Replace("'", "''") + "'");
            }
            string str = this.BuildQueryInSql<string>(dataValue.ToString().Split(new char[] { ',' }));
            return ("(" + str + ")");
        }

        private string valueToSQL(DatabaseType dbaseType, string typeName, object dataValue, DataFilterConditions queryCondition)
        {
            return this.valueToSQL(dbaseType, typeName, dataValue, queryCondition, false);
        }

        private string valueToSQL(DatabaseType dbaseType, string typeName, object dataValue, DataFilterConditions queryCondition, bool isBetweenEnd)
        {
            if (((dataValue == null) || (dataValue == DBNull.Value)) || (dataValue.ToString().Length == 0))
            {
                return "NULL";
            }
            if (!string.IsNullOrEmpty(typeName))
            {
                int index = typeName.IndexOf('.');
                if (index > 0)
                {
                    typeName = typeName.Substring(index + 1, (typeName.Length - index) - 1);
                }
                if (((queryCondition == DataFilterConditions.In) || (queryCondition == DataFilterConditions.NotIn)) || (queryCondition == DataFilterConditions.Special))
                {
                    return this.stringValueToSqlString(dataValue, queryCondition);
                }
                switch (typeName)
                {
                    case "Int16":
                    case "Int32":
                    case "Decimal":
                        {
                            decimal result = 0M;
                            decimal.TryParse(dataValue.ToString(), out result);
                            return result.ToString();
                        }
                    case "Boolean":
                        if ((dataValue == null) || ((string.Compare(dataValue.ToString(), "TRUE", true) != 0) && !(dataValue.ToString() == "1")))
                        {
                            return "0";
                        }
                        return "1";

                    case "String":
                        return this.stringValueToSqlString(dataValue, queryCondition);

                    case "DateTime":
                        {
                            DateTime time = DateTime.Parse(Convert.ToDateTime(dataValue).ToShortDateString());
                            DatabaseType type = dbaseType;
                            bool flag = ((time.Hour > 0) || (time.Minute > 0)) || (time.Second > 0);
                            string format = flag ? OrmConst.DATE_TIME_FORMATE : OrmConst.DATE_WITHOUT_TIME_FORMATE;
                            string str2 = flag ? OrmConst.DATABASE_DATE_TIME_FORMATE : OrmConst.DATABASE_WITHOUT_DATE_TIME_FORMATE;
                            if (type != DatabaseType.Oracle)
                            {
                                return ("'" + time.ToString(format) + "'");
                            }
                            if (!isBetweenEnd)
                            {
                                return ("to_Date('" + time.ToString(format) + "','" + str2 + "')");
                            }
                            string str3 = "(to_Date('" + time.ToString(format) + "','" + str2 + "'){0})";
                            return (flag ? string.Format(str3, "") : string.Format(str3, "+ 0.99999"));
                        }
                }
            }
            return this.stringValueToSqlString(dataValue, queryCondition);
        }

        // Properties
        public static Dictionary<string, string> HS_SQL_SPEC_STRING
        {
            get
            {
                return _HS_SQL_SPEC_STRING;
            }
        }

        public static SqlShareHelper Instance
        {
            get
            {
                if (_Instance == null)
                {
                    lock (_Obj)
                    {
                        if (_Instance == null)
                        {
                            _Instance = new SqlShareHelper();
                        }
                    }
                }
                return _Instance;
            }
        }
    }
}
